Difference Context Functions

As described in the context calculation overview, the Difference functions are designed to provide an easy technique to calculate the difference between data points in a result set. While these can be done in semantic formulas, they are much more appropriate as context logic, because they will change to suit the selections made in a report or visual - which is significantly more efficient.

Difference Logic

The core principal behind difference calc's is to compare data points in a query and easily determine the difference between the values as you move from one item to the next. Although this concept is extended to comparing to the first and last as well, the key element is that the logic looks at the list of items (from a hierarchy) and checks the movement of data from one "row" to the next on the selected metric. By changing the hierarchy or the selections, the differential will change accordingly, without needing to redefine or recode the calculation itself (i.e. its contextual).

Examples

ClosedExample 1. Difference to Previous, Difference to First

Using the grid below:

  • Difference to Previous: Apr 2016 to Mar 2016 represents an increase of 9837.97
  • Difference to First: Aug 2016 to Jan 2016, represents an increase of 3483.32

ClosedExample 2. Percentage of Next, Percentage of Last

Using the grid below:

  • Percentage of Next : Jan 2016 is 128.37% of the Feb 2016
  • Percentage of Last: Jun 2016 is 123.08% of Dec 2016

ClosedExample 3. Percentage Difference of Previous

Using the grid below:

  • Percentage Difference of Previous : The difference between Feb 2016 and Jan 2016, as a percent of Jan 2016 is -22.10% (-25,197.02 / 114,022.85)

ClosedExample 4. Using Difference Calcs to drive a Waterfall chart

One of the best use cases for the logic is to drive the Waterfall visualization.

The first step, involves plotting the measure on a chart with categories (usually time or something like accounts)

Next, add a difference calc based on cost, and use it to drive coloring for cost using the "positive / negative" option. In this way, bars that are going up compared to the prior month are green. Bars that are going down appear red.

In the last step, choose the waterfall visualization option.

And the chart will be transformed into a classic waterfall chart,